/**
 * Copyright 2011 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.google.api.adwords.starterkit.db.tables;

import com.google.api.adwords.starterkit.db.keys.AdGroupKey;
import com.google.api.adwords.starterkit.db.structures.AdGroupStructure;
import com.google.api.adwords.starterkit.utils.DumpExceptions;
import com.google.api.adwords.v201101.cm.AdGroupStatus;
import com.google.api.adwords.v201101.cm.UserStatus;

import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.HashMap;

/**
 * The Class AdGroupTable encapsulates most of AdGroups DB access.
 * 
 * @author rodrigo.f.vale@gmail.com  (Rodrigo Vale)
 */
public class AdGroupTable {
  private PreparedStatement prepNew;
  private PreparedStatement prepUpdate;
  private PreparedStatement prepLoad;
  private PreparedStatement prepTouched;
  private PreparedStatement prepUpdated;
  private PreparedStatement prepStatsUpdate;
  private PreparedStatement prepExists;
  private PreparedStatement prepLocalId;
  private PreparedStatement prepSetId;
  private PreparedStatement prepBatchUpdated; 
  private PreparedStatement prepResetIsBanned; 
  private PreparedStatement prepResetIsUpdated; 
  private PreparedStatement prepResetIsTouched; 
  
  private Connection conn;
  private static final Logger log = Logger.getLogger("StarterKit");
  private int batchCount = 0;
  
  /**
   * Instantiates a new ad group utils.
   *
   * @param conn the DB connection
   */
  public AdGroupTable(Connection conn) {
    this.conn = conn;
    try {
      Statement stat = conn.createStatement();
      stat.executeUpdate(
            "CREATE TABLE IF NOT EXISTS AdGroups "
          + "  ( "
          + "     LocalId             INTEGER PRIMARY KEY, "
          + "     AdGroupId           LONG DEFAULT 0, "
          + "     LocalCampaignId     LONG, "
          + "     CampaignId          LONG DEFAULT 0, "
          + "     MaxCpc              LONG DEFAULT 0, "
          + "     MaxCpm              LONG DEFAULT 0, "
          + "     Name                STRING, "
          + "     Status              STRING, "
          + "     State               INTEGER, "
          + "     IsUpdated           BOOLEAN DEFAULT 0, "
          + "     MaxContentCpc       INTEGER DEFAULT 0, "
          + "     SiteMaxCpc          INTEGER DEFAULT 0, "
          + "     MaxCpa              INTEGER DEFAULT 0, "
          + "     Ctr                 FLOAT DEFAULT 0, "
          + "     Clicks              INTEGER DEFAULT 0, "
          + "     Cost                FLOAT DEFAULT 0, "
          + "     Impressions         INTEGER DEFAULT 0, "
          + "     Conversions         INTEGER DEFAULT 0, "
          + "     Transactions       INTEGER DEFAULT 0, "
          + "     IsStatusModificable BOOLEAN DEFAULT 1, "
          + "     IsBidModificable    BOOLEAN DEFAULT 1, "
          + "     IsTouched           BOOLEAN DEFAULT 0, "
          + "     IsStatsAvailable    BOOLEAN DEFAULT 0, "
          + "     IsBanned            BOOLEAN DEFAULT 0, "
          + "     TargetCpa           LONG DEFAULT 0, "
          + "     LastUpdate          INTEGER, "
          + "     AveragePosition     FLOAT DEFAULT 0, "
          + "     AdGroupMaxCpa       FLOAT DEFAULT 0, "
          + "     CostPerConverstion  FLOAT DEFAULT 0, "
          + "     CostPerTransaction  FLOAT DEFAULT 0, "
          + "     FirstPageCpc        FLOAT  DEFAULT 0"          
          + "  ); ");
      stat.executeUpdate(
            "CREATE INDEX IF NOT EXISTS AdGroupsLocalCampaigIdNameIndex " 
          + "on adgroups (LocalCampaignId, Name);");
      stat.executeUpdate("CREATE INDEX IF NOT EXISTS AdGroupsAdGroupIdIndex " 
          + "ON AdGroups (AdGroupId);");
      
      prepLocalId = conn.prepareStatement(
          "SELECT LocalId FROM AdGroups WHERE AdGroupId = ?;"); 
      prepExists = conn.prepareStatement(
          "SELECT Name FROM AdGroups WHERE Name = ? AND LocalCampaignId = ?;");
      
      prepLoad = conn.prepareStatement(
          "SELECT * FROM AdGroups WHERE Name = ? AND LocalCampaignId = ?;"); 

      prepTouched = conn.prepareStatement(
            "UPDATE AdGroups SET IsTouched = ? " 
          + "WHERE LocalCampaignId = ? AND Name = ?;"); 
      prepUpdated = conn.prepareStatement(
            "UPDATE AdGroups SET IsUpdated = ? " 
          + "WHERE Name = ? AND LocalCampaignId = ?;"); 
      prepBatchUpdated = conn.prepareStatement(
            "UPDATE AdGroups SET IsUpdated = ?, IsTouched = ? "
          + "WHERE Name = ? AND LocalCampaignId = ?;"); 
      prepSetId = conn.prepareStatement(
            "UPDATE AdGroups SET AdGroupId = ?, IsUpdated = ?, IsTouched = ? " 
          + "WHERE Name = ? and LocalCampaignId = ?;"); 

      prepNew = conn.prepareStatement(
            "INSERT INTO AdGroups (AdGroupId, LocalCampaignId, CampaignId, " 
          + "MaxCpc, MaxCpm, Name, Status, MaxContentCpc, SiteMaxCpc, MaxCpa, "
          + "Ctr, Clicks, Cost, Impressions, Conversions, Transactions, "  
          + "IsStatusModificable, IsBidModificable, IsUpdated, " 
          + "IsStatsAvailable, IsBanned, TargetCpa, IsTouched, LastUpdate) "
          + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " 
          + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);"); 
      prepUpdate = conn.prepareStatement(
            "REPLACE INTO AdGroups (LocalId, AdGroupId, LocalCampaignId, " 
          + "CampaignId, MaxCpc, MaxCpm, Name, Status, MaxContentCpc, " 
          + "SiteMaxCpc, MaxCpa, Ctr, Clicks, Cost, Impressions, Conversions, " 
          + "Transactions, IsStatusModificable, IsBidModificable, IsUpdated, "
          + "IsStatsAvailable, IsBanned, TargetCpa, IsTouched, LastUpdate) "
          + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " 
          + "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");
      
      prepStatsUpdate = conn.prepareStatement(
          "UPDATE adgroups SET Ctr = ?, Clicks = ?, Cost = ?, "
        + "Impressions = ?, Conversions = ?, Transactions = ?, "
        + "AveragePosition = ?, AdGroupMaxCpa = ?, "
        + "CostPerConverstion = ?, "
        + "CostPerTransaction = ?, "
        + "FirstPageCpc = ?, IsStatsAvailable = 1 WHERE adGroupId = ?");
      
      prepResetIsBanned = conn.prepareStatement(
          "UPDATE AdGroups SET IsBanned = ?");
      prepResetIsUpdated = conn.prepareStatement(
          "UPDATE AdGroups SET IsUpdated = ?");
      prepResetIsTouched = conn.prepareStatement(
          "UPDATE AdGroups SET IsTouched = ?");
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
      System.exit(1);
    }    
  }
  
  /**
   * Gets the AdGroupStructure from a generic SQL request.
   *
   * @param rs the ResultSet of a generic SQL request
   * @return all fields managed by AdGroup wrapper 
   */
  public AdGroupStructure getResultSet(ResultSet rs) {
    AdGroupStructure adGroupStructure = new AdGroupStructure();
    try {
      adGroupStructure.setStatus(rs.getString("Status"));
      adGroupStructure.setLocalId(rs.getInt("LocalId"));
      adGroupStructure.setAdGroupId(rs.getLong("AdGroupId"));
      adGroupStructure.setLocalCampaignId(rs.getInt("LocalCampaignId"));
      adGroupStructure.setCampaignId(rs.getLong("CampaignId"));
      adGroupStructure.setMaxCpc(rs.getLong("MaxCpc"));
      adGroupStructure.setMaxCpm(rs.getLong("MaxCpm"));
      adGroupStructure.setName(rs.getString("Name"));
      adGroupStructure.setMaxContentCpc(rs.getLong("MaxContentCpc"));
      adGroupStructure.setSiteMaxCpc(rs.getLong("SiteMaxCpc"));
      adGroupStructure.setMaxCpa(rs.getLong("MaxCpa"));
      adGroupStructure.setCtr(rs.getFloat("Ctr"));
      adGroupStructure.setClicks(rs.getLong("Clicks"));
      adGroupStructure.setCost(rs.getFloat("Cost"));
      adGroupStructure.setImpressions(rs.getLong("Impressions"));
      adGroupStructure.setConversions(rs.getLong("Conversions"));
      adGroupStructure.setTransactions(rs.getLong("Transactions"));
      adGroupStructure
          .setDefaultCpcModificable(rs.getBoolean("IsBidModificable"));
      adGroupStructure.setStatusModifiable(rs.getBoolean("IsStatusModificable"));
      adGroupStructure.setStatsAvailable(rs.getBoolean("IsStatsAvailable"));
      adGroupStructure.setBanned(rs.getBoolean("IsBanned"));
      adGroupStructure.setTargetCpa(rs.getLong("TargetCpa"));
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
    return adGroupStructure;
  }  
  
  /**
   * Loads a AdGroup from DB based on a primary key
   *
   * @param adGroupKey the ad group primary key
   * @return the ad group structure
   */
  public AdGroupStructure get(AdGroupKey adGroupKey) {
    ResultSet rs = null;
    AdGroupStructure adGroupStructure = null;
    try {
      prepLoad.setString(1, adGroupKey.getName());      
      prepLoad.setInt(2, adGroupKey.getLocalCampaignId());
      rs = prepLoad.executeQuery();
      if (rs.next()) {
        adGroupStructure = getResultSet(rs);
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return adGroupStructure;
  }
  
  /**
   * Check to see if the AdGroup exists.
   *
   * @param adGroupKey the ad group key
   * @return true, if successful
   */
  public boolean exists(AdGroupKey adGroupKey) {
    boolean exists = false;
    ResultSet rs = null;
    try {
      prepExists.setString(1, adGroupKey.getName());      
      prepExists.setInt(2, adGroupKey.getLocalCampaignId());
      rs = prepExists.executeQuery();
      if (rs.next()) {
        exists = true;
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return exists;
  }  

  /**
   * Save an AdGroup to DB
   *
   * @param adGroupStructure the ad group structure
   * @param updated flag to point that the AdGroup has an update to AdWords
   * @param touched flag to point that the AdGroup has been touched by 
   *        StarterKit
   * @return the ad group structure with local id from db
   */
  public AdGroupStructure save(AdGroupStructure adGroupStructure, 
      boolean updated, boolean touched) {
    ResultSet rs = null;
    try {
      PreparedStatement prep;
      int count = 1;
      if (adGroupStructure.getLocalId() == 0) {
        prep = prepNew;
      } else {
        prep = prepUpdate;
        prep.setLong(count++, adGroupStructure.getLocalId());
      }        
      prep.setLong(count++, adGroupStructure.getAdGroupId());
      prep.setInt(count++, adGroupStructure.getLocalCampaignId());
      prep.setLong(count++, adGroupStructure.getCampaignId());            
      prep.setLong(count++, adGroupStructure.getMaxCpc());
      prep.setLong(count++, adGroupStructure.getMaxCpm());
      prep.setString(count++, adGroupStructure.getName());
      prep.setString(count++, adGroupStructure.getStatus().getValue());
      prep.setLong(count++, adGroupStructure.getMaxContentCpc());
      prep.setLong(count++, adGroupStructure.getSiteMaxCpc());
      prep.setLong(count++, adGroupStructure.getMaxCpa());
      prep.setFloat(count++, adGroupStructure.getCtr());
      prep.setLong(count++, adGroupStructure.getClicks());
      prep.setFloat(count++, adGroupStructure.getCost());
      prep.setLong(count++, adGroupStructure.getImpressions());
      prep.setLong(count++, adGroupStructure.getConversions());
      prep.setLong(count++, adGroupStructure.getTransactions());
      prep.setBoolean(count++, adGroupStructure.isStatusModifiable());
      prep.setBoolean(count++, adGroupStructure.isDefaultCpcModificable());
      prep.setBoolean(count++, updated);
      prep.setBoolean(count++, adGroupStructure.isStatsAvailable());
      prep.setBoolean(count++, adGroupStructure.isBanned());
      prep.setLong(count++, adGroupStructure.getTargetCpa());
      prep.setBoolean(count++, touched);
      prep.setLong(count++, new Date().getTime());
      prep.executeUpdate();

      rs = prep.getGeneratedKeys();      
      adGroupStructure.setLocalId(rs.getInt(1));  
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    } 
    return adGroupStructure;
  }
  
  /**
   * Save an AdGroup to DB using a Batch mode
   *
   * @param adGroupStructure the ad group structure
   * @param updated flag to point that the AdGroup has an update to AdWords
   * @param touched flag to point that the AdGroup has been touched by 
   *        StarterKit
   */
  public void saveBatch(AdGroupStructure adGroupStructure, 
      boolean updated, boolean touched) {
    try {
      PreparedStatement prep;
      int count = 1;
      if (adGroupStructure.getLocalId() == 0) {
        prep = prepNew;
      } else {
        prep = prepUpdate;
        prep.setLong(count++, adGroupStructure.getLocalId());
      }        
      prep.setLong(count++, adGroupStructure.getAdGroupId());
      prep.setInt(count++, adGroupStructure.getLocalCampaignId());
      prep.setLong(count++, adGroupStructure.getCampaignId());            
      prep.setLong(count++, adGroupStructure.getMaxCpc());
      prep.setLong(count++, adGroupStructure.getMaxCpm());
      prep.setString(count++, adGroupStructure.getName());
      prep.setString(count++, adGroupStructure.getStatus().getValue());
      prep.setLong(count++, adGroupStructure.getMaxContentCpc());
      prep.setLong(count++, adGroupStructure.getSiteMaxCpc());
      prep.setLong(count++, adGroupStructure.getMaxCpa());
      prep.setFloat(count++, adGroupStructure.getCtr());
      prep.setLong(count++, adGroupStructure.getClicks());
      prep.setFloat(count++, adGroupStructure.getCost());
      prep.setLong(count++, adGroupStructure.getImpressions());
      prep.setLong(count++, adGroupStructure.getConversions());
      prep.setLong(count++, adGroupStructure.getTransactions());
      prep.setBoolean(count++, adGroupStructure.isStatusModifiable());
      prep.setBoolean(count++, adGroupStructure.isDefaultCpcModificable());
      prep.setBoolean(count++, updated);
      prep.setBoolean(count++, adGroupStructure.isStatsAvailable());
      prep.setBoolean(count++, adGroupStructure.isBanned());
      prep.setLong(count++, adGroupStructure.getTargetCpa());
      prep.setBoolean(count++, touched);
      prep.setLong(count++, new Date().getTime());
      prep.addBatch();
      
      batchCount++;
      if (batchCount == 100000) {
        batchCount = 0;
        execBatch();
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } 
  }
      
  /**
   * Informs to StarterKit that the AdGroup was processed by the system.
   * This avoid to starterkit to pause the AdGroup automatically when 
   * "AutoPause" in settings file is true
   *
   * @param adGroupKey the adgroup primary key
   * @param touched boolean flag 
   */
  public void setTouched(AdGroupKey adGroupKey, boolean touched) {
    try {
      prepTouched.setBoolean(1, touched);
      prepTouched.setInt(2, adGroupKey.getLocalCampaignId());
      prepTouched.setString(3, adGroupKey.getName());
      prepTouched.execute();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }
  
  /**
   * Gets the local id from DB based on the AdGroupId from AdWords.
   *
   * @param adGroupId the ad group id from AdWords
   * @return the local id from DB
   */
  public int getLocalId(long adGroupId) {
    ResultSet rs = null;
    int localId = 0;
    try { 
      prepLocalId.setLong(1, adGroupId);
      rs = prepLocalId.executeQuery();
      if (rs.next()) {
        localId = rs.getInt("LocalId");
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }     
    return localId;
  }

  /**
   * Informs to StarterKit that the AdGRoup was updated by the application.
   * During the AdWords synchronization process only updated AdGroups will 
   * be processed by StarterKit.
   *  
   * @param adGroupKey the adgroup primary key
   * @param updated boolean flag 
   */
  public void setUpdated(AdGroupKey adGroupKey, boolean updated) {
    try { 
      prepUpdated.setBoolean(1, updated);
      prepUpdated.setString(2, adGroupKey.getName());
      prepUpdated.setInt(3, adGroupKey.getLocalCampaignId());
      prepUpdated.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }
  
  /**
   * Resets IsBanned flag in all rows of AdGroups table
   */
  public void resetIsBanned(boolean isBanned) {
    try {
      prepResetIsBanned.setBoolean(1, isBanned);
      prepResetIsBanned.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }     
  }
  
  /**
   * Resets IsUpdated flag in all rows of AdGroups table.
   */
  public void resetIsUpdated(boolean isUpdated) {
    try {
      prepResetIsUpdated.setBoolean(1, isUpdated);
      prepResetIsUpdated.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }     
  }

  /**
   * Resets IsUpdated flag in all rows of AdGroups table.
   */
  public void resetIsTouched(boolean isTouched) {
    try {
      prepResetIsTouched.setBoolean(1, isTouched);
      prepResetIsTouched.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }     
  }
  
  /**
   * Set the Status to PAUSED for all AdGroups not touched by the 
   * application.
   */
  public void autoPause() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE AdGroups SET Status = ? WHERE isTouched = 0 " +
            "AND Status NOT IN (?, ?)");
      prep.setString(1, UserStatus.PAUSED.getValue());
      prep.setString(2, UserStatus.PAUSED.getValue());
      prep.setString(3, UserStatus.DELETED.getValue());
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
  }

  /**
   * Add a SQL to the Batch process. In this case, it will update both 
   * updated and touched flags.
   *
   * @param adGroupKey the ad group primary key
   * @param updated the updated flag
   * @param touched the touched flag
   */
  public void setBatchUpdated(AdGroupKey adGroupKey, boolean updated, 
      boolean touched) {
    try { 
      prepBatchUpdated.setBoolean(1, updated);
      prepBatchUpdated.setBoolean(2, touched);
      prepBatchUpdated.setString(3, adGroupKey.getName());
      prepBatchUpdated.setInt(4, adGroupKey.getLocalCampaignId());
      prepBatchUpdated.addBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }

  /**
   * Executes all batch process in the pipeline
   */
  public void execBatch() {
    try {
      prepNew.executeBatch();
      prepUpdate.executeBatch();
      prepSetId.executeBatch();
      prepBatchUpdated.executeBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }

  /**
   * Add a SQL to the Batch process. In this case, it will update both 
   * updated and touched flags also the AdGroupId from AdWords
   *
   * @param adGroupKey the ad group primary key
   * @param adGroupId the AdGroupId from AdWords
   * @param updated the updated flag
   * @param touched the touched flag
   */
  public void setBatchId(AdGroupKey adGroupKey, long adGroupId, boolean updated,
      boolean touched) {
    try { 
      prepSetId.setLong(1, adGroupId);
      prepSetId.setBoolean(2, updated);
      prepSetId.setBoolean(3, touched);      
      prepSetId.setString(4, adGroupKey.getName());
      prepSetId.setInt(5, adGroupKey.getLocalCampaignId());
      prepSetId.addBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }

  /**
   * Reset all adgroups flags to its default values 
   */
  public void reset() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE AdGroups SET IsUpdated = 0, IsTouched = 0, "
          + "IsStatusModificable = 1, IsBidModificable = 1;");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      try {
        if (prep != null) {
          prep.close();
        }
      } catch (SQLException e) {
        DumpExceptions.dumpSQLException(e);
      }
    }        
  }
  
  /**
   * Gets all BiddingStrategy from Campaigns mapped to AdGroup LocalId
   */
  public HashMap<Integer, String> getCampaignsBiddingStrategy() {
    HashMap<Integer, String> biddingMap = new HashMap<Integer, String>(); 
    PreparedStatement prep = null;
    ResultSet rs = null;
    try {
      prep = conn.prepareStatement(
            "SELECT DISTINCT ad.LocalId, ca.BiddingStrategy " 
          + "FROM Campaigns ca, AdGroups ad " 
          + "WHERE ad.LocalCampaignId = ca.LocalId;");
      rs = prep.executeQuery();
      while (rs.next()) {
        int localId = rs.getInt("LocalId");
        String biddingStrategy = rs.getString("BiddingStrategy");
        biddingMap.put(localId, biddingStrategy);
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return biddingMap;
  }
  
  /**
   * Update campaigns ids at ad group table
   */
  public void updateCampaignsIds() {
    log.debug("Updating CampaignId in AdGroups table");    
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
          "UPDATE AdGroups SET CampaignId = ? WHERE LocalCampaignId = ?");
      HashMap<Integer, Long> campaignsIdMap = getNewCampaignsIds();
      int count = 0;
      for (int localCampaignId : campaignsIdMap.keySet()) {
        long campaignId = campaignsIdMap.get(localCampaignId);
        prep.setLong(1, campaignId);
        prep.setInt(2, localCampaignId);
        prep.addBatch();
        count++;
        if (count == 100000) {
          count = 0;
          prep.executeBatch();
        }
      }
      prep.executeBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
  }  
  
  /**
   * Gets the campaign ids map for new campaigns
   *
   * @return the campaign ids map
   */
  public HashMap<Integer, Long> getNewCampaignsIds() {
    log.debug("Loading Campaigns Ids needed by new AdGroups");
    HashMap<Integer, Long> campaignsIdsMap = new HashMap<Integer, Long>(); 
    ResultSet rs = null;
    try {
      Statement stat = conn.createStatement();
      rs = stat.executeQuery(
            "SELECT DISTINCT ca.LocalId, ca.CampaignId FROM Campaigns ca, "
          + "AdGroups Ag WHERE Ag.LocalCampaignId = Ca.LocalId AND "
          + "Ag.CampaignId = 0;");
      while (rs.next()) {
        campaignsIdsMap.put(rs.getInt("LocalId"), rs.getLong("CampaignId"));
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return campaignsIdsMap;
  }
  
  public ResultSet getModified() {
    log.debug("Loading DB");
    ResultSet rs = null;
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
          "SELECT * FROM AdGroups WHERE IsUpdated = 1 AND IsBanned = 0");
      rs = prep.executeQuery();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
    return rs;
  }

  /**
   * Grant that all banned AdGroups will not be updated by StarterKit
   * synchronization process.
   */
  public void grantBanned() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE AdGroups SET IsUpdated = 0, IsTouched = 0 " +
            "WHERE IsBanned = 1");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
  }

  /**
   * Maps AdGroupId into LocalId
   */
  public HashMap<Long, Integer> getAdGroupIdsMap() {
    log.debug("Loading AdGroupId -> LocalId");
    HashMap<Long, Integer> adGroupIdsMap = new HashMap<Long, Integer>();
    ResultSet rs = null;
    Statement stat = null;
    try {
      stat = conn.createStatement();
      rs = stat
          .executeQuery("SELECT LocalId, AdGroupId FROM AdGroups;");
      while (rs.next()) {
        adGroupIdsMap.put(rs.getLong("AdGroupId"), rs.getInt("LocalId"));
      }
      rs.close();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
      if (stat != null) {
        try {
          stat.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return adGroupIdsMap;
  }
  
  /**
   * Gets all AdGroups with Stats information grouped by Campaign
   */
  public ResultSet getStats() {
    PreparedStatement prep = null;
    ResultSet rs = null;
    try {
      prep = conn.prepareStatement(
          "SELECT campaignId, avg(Ctr) Ctr, SUM(Clicks) Clicks, "
        + "SUM(Cost) Cost, SUM(Impressions) Impressions, "
        + "SUM(Conversions) Conversions, SUM(Transactions) Transactions, "
        + "AVG(averagePosition) AveragePosition, "
        + "AVG(adGroupMaxCpa) AdGroupMaxCpa, "
        + "AVG(costPerConverstion) CostPerConverstion, "
        + "AVG(costPerTransaction) CostPerTransaction, "
        + "AVG(firstPageCpc) FirstPageCpc "
        + "FROM AdGroups WHERE IsStatsAvailable = 1 GROUP BY CampaignId;");
      rs = prep.executeQuery();    
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
    return rs;
  }
  
  public void saveStatsBatch(float ctr, long clicks, float cost, 
      long impressions, long conversions, long transactions, 
      float averagePosition, float adGroupMaxCpa, float firstPageCpc,
      long adGroupId) {
    try {
      prepStatsUpdate.setFloat(1, ctr);
      prepStatsUpdate.setLong(2, clicks);
      prepStatsUpdate.setFloat(3, cost);
      prepStatsUpdate.setLong(4, impressions);
      prepStatsUpdate.setLong(5, conversions);
      prepStatsUpdate.setLong(6, transactions);
      prepStatsUpdate.setDouble(7, averagePosition);
      prepStatsUpdate.setDouble(8, adGroupMaxCpa);

      if (conversions > 0) {
        prepStatsUpdate
        .setDouble(9, cost / conversions);
      } else {
        prepStatsUpdate.setDouble(9, 0);
      }
      if (transactions > 0) {
        prepStatsUpdate.setDouble(10, cost / transactions);
      } else {
        prepStatsUpdate.setDouble(10, 0);
      }
      prepStatsUpdate.setDouble(11, firstPageCpc);      
      prepStatsUpdate.setLong(12, adGroupId);

      prepStatsUpdate.addBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }    
  }  
  
  public void execStatsBatch() {
    try {
      prepStatsUpdate.executeBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  } 

  public void resetStats() {
    Statement stat = null;
    try {
      stat = conn.createStatement();
      stat.executeUpdate(
            "UPDATE AdGroups SET Ctr = 0, Clicks = 0, Cost = 0, "
          + "Impressions = 0, Conversions = 0, Transactions = 0, "
          + "IsStatsAvailable =  0;");
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (stat != null) {
        try {
          stat.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
  }

  /**
   * Reset all campaigns status flags to its default values
   */
  public void resetStatus() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE AdGroups SET IsUpdated = 0, IsTouched = 0");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }     
  }

  /**
   * Get an AdGroup from DB based on its LocalId 
   */
  public AdGroupStructure get(int localId) {
    PreparedStatement prep = null;
    AdGroupStructure adGroupStructure = null; 
    try {
      prep = conn.prepareStatement(
            "SELECT * FROM AdGroups WHERE localId = ?");
      prep.setInt(1, localId);
      ResultSet rs = prep.executeQuery();
      adGroupStructure = getResultSet(rs);
      rs.close();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }     
    return adGroupStructure;
  }
}